import pymysql.cursors

CATA_TABLE = """
create table if not exists catagory(
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(63) NOT NULL UNIQUE,
    PRIMARY KEY(id)
)
"""

SUB_TABLE = """
create table if not exists subject(
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(63) NOT NULL UNIQUE,
    code varchar(15) NOT NULL UNIQUE,
    cata_id int(11) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY fk_cata(cata_id) REFERENCES catagory(id)
)
"""

UNIVERSITY_TABLE = """
create table if not exists university (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(15) NOT NULL UNIQUE,
    code varchar(15) NOT NULL UNIQUE,
    PRIMARY KEY(id)
)
"""

RECORD_TABLE = """
create table if not exists record(
    id int(11) NOT NULL AUTO_INCREMENT,
    sub_id int(11) NOT NULL,
    uni_id int(11) NOT NULL,
    ranking int(11) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY fk_sub(sub_id) REFERENCES subject(id),
    FOREIGN KEY fk_uni(uni_id) REFERENCES university(id),
    UNIQUE KEY (sub_id,uni_id,ranking)
)
"""

connection = pymysql.connect(
    user="root",
    password="",
    db="spider"
)


def create_table():
    try:
        with connection.cursor() as cursor:
            cursor.execute(CATA_TABLE)
            cursor.execute(SUB_TABLE)
            cursor.execute(UNIVERSITY_TABLE)
            cursor.execute(RECORD_TABLE)
        connection.commit()
    finally:
        connection.close()


if __name__ == "__main__":

    create_table()
